from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app =Flask(__name__)


# 设置数据库配置信息
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://root:123456@127.0.0.1:3306/test"
# 增加开销，设置false。不会追踪数据库
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db =SQLAlchemy(app)

# 角色（一方）
class Role(db.Model):
    __tablenamne__ = "roles"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(32))
    # 给role添加user属性，查询方式：role.users
    users = db.relationship("User")
    # 给user添加role属性，查询方式：user.role
    # users和role都不会产生实体字段
    # lazy：只有用到才会查询。因为每次relationship都是子查询
    users = db.relationship("User", backref="role",lazy="dynamic") #  role.users.all()
    # 如果类继承自object那么重写__str__方法即可，如果是继承自db.Model，那么继承__repr__方法
    def __repr__(self):
        return "<Role:%s>"%self.name

# 用户（多方）
class User(db.Model):
    __tablenamne__ = "users"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(32))
    email = db.Column(db.String(32))
    password = db.Column(db.String(32))

    # 建立外键 关系
    role_id = db.Column(db.Integer,db.ForeignKey(Role.id))
    def __repr__(self):
        return "<User:%s,%s,%s,%s>"%(self.id,self.name,self.email,self.password)

@app.route("/")
def hello():
    return "hello"

if __name__ == '__main__':
    db.drop_all()
    db.create_all()
    # 创建测试数据
    ro1 = Role(name='admin')
    db.session.add(ro1)
    db.session.commit()
    # 再次插入一条数据
    ro2 = Role(name='user')
    db.session.add(ro2)
    db.session.commit()

    # 多条用户数据
    us1 = User(name='wang', email='wang@163.com', password='123456', role_id=ro1.id)
    us2 = User(name='zhang', email='zhang@189.com', password='201512', role_id=ro2.id)
    us3 = User(name='chen', email='chen@126.com', password='987654', role_id=ro2.id)
    us4 = User(name='zhou', email='zhou@163.com', password='456789', role_id=ro1.id)
    us5 = User(name='tang', email='tang@itheima.com', password='158104', role_id=ro2.id)
    us6 = User(name='wu', email='wu@gmail.com', password='5623514', role_id=ro2.id)
    us7 = User(name='qian', email='qian@gmail.com', password='1543567', role_id=ro1.id)
    us8 = User(name='liu', email='liu@itheima.com', password='867322', role_id=ro1.id)
    us9 = User(name='li', email='li@163.com', password='4526342', role_id=ro2.id)
    us10 = User(name='sun', email='sun@163.com', password='235523', role_id=ro2.id)
    db.session.add_all([us1, us2, us3, us4, us5, us6, us7, us8, us9, us10])
    db.session.commit()
    app.run(debug=True)